package main

import (
	"gorm.io/gorm"
)

//type Students struct {
//	ID     uint    `gorm:"size:10"`
//	Name   string  `gorm:"size:16;not null;comment:姓名"`
//	Age    int     `gorm:"size:3;not null;comment:年龄"`
//	Email  *string `gorm:"size:128;comment:邮箱;unique"` //使用指针是为了存空值
//	Gender bool
//}

func main() {
	//DB.AutoMigrate(&Students{})
	//var studentList []Students
	//DB.Find(&studentList).Delete(&studentList)
	//studentList = []Students{
	//	{ID: 1, Name: "李元芳", Age: 32, Email: PtrString("lyf@yf.com"), Gender: true},
	//	{ID: 2, Name: "张武", Age: 18, Email: PtrString("zhangwu@lly.cn"), Gender: true},
	//	{ID: 3, Name: "枫枫", Age: 23, Email: PtrString("ff@yahoo.com"), Gender: true},
	//	{ID: 4, Name: "刘大", Age: 54, Email: PtrString("liuda@qq.com"), Gender: true},
	//	{ID: 5, Name: "李武", Age: 23, Email: PtrString("liwu@lly.cn"), Gender: true},
	//	{ID: 6, Name: "李琦", Age: 14, Email: PtrString("liqi@lly.cn"), Gender: false},
	//	{ID: 7, Name: "晓梅", Age: 25, Email: PtrString("xiaomeo@sl.com"), Gender: false},
	//	{ID: 8, Name: "如燕", Age: 26, Email: PtrString("ruyan@yf.com"), Gender: false},
	//	{ID: 9, Name: "魔灵", Age: 21, Email: PtrString("moling@sl.com"), Gender: true},
	//}
	//DB.Create(&studentList)
	/*
		#查询用户名是枫枫的
		select *  from  students where name='枫枫';
		#查询用户名不是枫枫的
		select *  from  students where not name  ='枫枫';
		#查询用户名包含 如燕、李元芳的
		select * from students where name in ('如燕','元芳');
		#查询姓李的 %号代表多个占位符 一个_下划线代表一个占位符
		select * from students where name like '李%';
		# 查询年龄大于23，是qq邮箱的
		select * from students where age>23 and email like '%@qq.com';
		#查询是qq邮箱的，或者是女的
		select * from students where email like '%@qq.com' or gender =false;
	*/
	DB = DB.Session(&gorm.Session{
		Logger: mysqlLogger,
	})
	//var students []Students
	/*
		#查询用户名是枫枫的
		DB.Where("name = ?", "枫枫").Find(&students) //[2.258ms]
		fmt.Println(students)
		DB.Find(&students, "name = ?", "枫枫") //速度快 [1.959ms]
		fmt.Println(students)
	*/
	/*
		#查询用户名不是枫枫的 总条数
		fmt.Println(DB.Not("name =?", "枫枫").Find(&students).RowsAffected)
		fmt.Println(DB.Where("not name =?","枫枫").Find(&students).RowsAffected)
	*/
	/*
		#查询用户名包含 如燕、李元芳的
		DB.Where("name in ?", []string{"如燕", "李元芳"}).Find(&students)
		DB.Find(&students, "name in ?", []string{"如燕", "李元芳"})
		fmt.Println(students)
	*/
	/*
		#查询姓李的
		DB.Where("name like ?", "李%").Find(&students)
			DB.Where("name like ?", "李_").Find(&students) //查询以李姓开头的两个字的名称
			fmt.Println(students)
	*/
	/*
		# 查询年龄大于23，是qq邮箱的
		DB.Where("age >23 and email like ?", "%@qq.com").Find(&students)
		DB.Where("age>23").Where("email like ?","%@qq.com").Find(&students)
		fmt.Println(students)
	*/
	/*
		   查询是qq邮箱的，或者是女的
			DB.Where("gender=? or email like ?", false, "%@qq.com").Find(&students)
			DB.Where("gender=?",false).Or("email like ?", "%@qq.com").Find(&students)
			fmt.Println(students)
	*/
	/*
		使用结构体查询，会过滤零值  并且结构体中的条件都是and关系
		DB.Where(&Students{Name: "李元芳", Age: 0}).Find(&students)
		fmt.Println(students)
	*/
	/*
		使用map查询 不会过滤零值
		DB.Where(map[string]any{"name": "如燕", "age": 32}).Find(&students)
		fmt.Println(students)
	*/
	/*
		select选择字段展示，其余字段会展示对应数据类型的零值
		DB.Select("name","age").Find(&students)
		DB.Select([]string{"name","age"}).Find(&students)
		fmt.Println(students)
	*/
	/*
		查找想要的字段数据并通过Scan写入到新的结构体中
		type User struct {
			Name string
			Age  int
		}
		var userList []User
		DB.Select("name", "age").Find(&students).Scan(&userList) //会经历两次查询，先查找students表中的name和age字段，然后在把信息写入到userList中
		DB.Model(Students{}).Select("name", "age").Scan(&userList) //只会经历一次查询然后直接写入userList
		fmt.Println(userList)
	*/
	/*
		根据年龄排序
		DB.Order("age desc").Find(&students) //降序
		DB.Order("age asc").Find(&students) //升序
		fmt.Println(students)
	*/
	/*
		分页的写法
		//DB.Limit(2).Offset(0).Find(&students) //查询第一页的两条数据
		//DB.Limit(2).Offset(2).Find(&students) //查询第二页的两条数据
		//DB.Limit(2).Offset(4).Find(&students) //查询第三页的两条数据
		//DB.Limit(2).Offset(6).Find(&students) //查询第四页的两条数据
		//分页的通用写法 实际中会使用这个来进行分页
		limit := 2 //每页显示几条数据
		page := 1  //页数
		DB.Limit(limit).Offset((page - 1) * limit).Find(&students)
		fmt.Println(students)
	*/
	/*
		年龄去重并将去重后的数据写入到新结构体中
		var ageList []int
		//DB.Model(Students{}).Select("age").Distinct("age").Scan(&ageList) //第一种去重方法
		DB.Model(Students{}).Select("distinct age").Scan(&ageList) //第二种去重方法
		fmt.Println(ageList)
	*/
	/*
		分组查找男生与女生的个数
		type Group struct {
			Count  int
			Gender string
		}
		var groupList []Group
		DB.Model(Students{}).Select("count(id) as count", "gender").Group("gender").Scan(&groupList)
		fmt.Println(groupList)
	*/
	/*
		分组 查找男生、女生的人数并打印出对应的人名
			type Group struct {
				Count    int
				Gender   string
				NameList string
			}
			var groupList []Group
			DB.Model(Students{}).Select(
				"count(id) as count",
				"gender",
				"group_concat(name) as name_list",
			).
				Group("gender").Scan(&groupList)
			fmt.Println(groupList) //结果 SELECT count(id) as count,`gender`,group_concat(name) as name_list FROM `students` GROUP BY `gender` [{3 0 李琦,晓梅,如燕} {6 1 李元芳,张武,枫枫,刘大,李武,魔灵}]
	*/
	/*
		执行原生sql 进行分组
		type Group struct {
			Count    int
			Gender   string
			NameList string
		}
		var groupList []Group
		DB.Raw("SELECT count(id) as count,`gender`,group_concat(name) as name_list FROM `students` GROUP BY `gender`").Scan(&groupList)
		fmt.Println(groupList)
	*/
}

func PtrString(email string) *string {
	return &email
}
